Athena で S3 バケット内のファイル名に含まれるタイムスタンプから同一日付の新しいレコードのみを抽出してみる

Athena で S3 バケット内のファイル名に含まれるタイムスタンプから同一日付の新しいレコードのみを抽出してみる

Clock Icon2024.07.02

いわさです。

Athena で日次出力される CSV ファイルを参照しているケースで、ファイルに作成時点のタイムスタンプが含まれるために同一日付で CSV ファイルの再作成をするとバージョニングが効かないケースがありました。

次は適用に用意したサンプルなのですが、こんな感じでファイル末尾にタイムスタンプ情報が含まれています。
以下であれば、7月1日のファイルがひとつと、7月2日のファイルが0時のものと1時のものが存在しています。

E0BE3235-10C4-428B-BDBF-961F03A92AC5

この時、7月2日0時のものは古いデータとして無視し、1時のデータだけ扱いたいというケースがありました。
データ内容は構造は同じですが次のような感じになっています。

9A87E056-24A7-4697-8D65-9A212275F2A9_4_5005_c

普通に取得すると重複したレコードも取得されるので、9件取得されます。

EC9FE0DB-A67A-464A-AC66-47D173306325_1_105_c

最新の CSV がアップロードされたタイミングで S3 トリガー Lambda などで同日日付の古いファイルを判定して削除する方法でも良いかなと思ったのですが、Lambda コードの実装が必要になるため、まずは Athena だけでどうにか出来ないかと試みることにしました。

パスを取得する

まず、データにタイムスタンプ情報が無かったり、日時パーティションの設定がなかったりする場合はファイル名からその情報を取得する必要があります。

どうやら$pathを使うとファイルパスの取得が出来るのでこれを使ってみたいと思います。

https://dev.classmethod.jp/articles/amazon-athena-path-hidden-column/

ファイルパスをフィールドとして取得する様子。

ACEB98F1-63DC-414E-9CAF-1A055F8333CB_1_105_c

同一日時の最新時間のレコードのみを抽出する

ここからはいくつか手法がありそうですが、今回は各日時の最新時間のキーを取得したものを内部結合することで古いレコードを除外するというのをやってみました。

今回は日付と時間のフィールドは固定で文字数で切り出したものをサブクエリで結合しているだけです。
DISTINCT も考えましたが、重複除外しつつ特定レコードの値を取得するのが難しそうだったのでこの方式にしました。

With
    hoge AS (
        SELECT
            date_parse("$path", 's3://hoge0702athena/fuga_daily_%Y%m%d%H%i.csv') as hogetimestamp,
            substring("$path", 32, 8) as hogedate,
            substring("$path", 40, 4) as hogetime,
            col1,
            val1
        FROM "default"."hoge0702hoge0702athena"
    )
SELECT
    hogetimestamp,
    col1,
    val1
FROM hoge INNER JOIN (
    SELECT
        hogedate,
        MAX(hogetime) as hogetime
    FROM hoge GROUP BY hogedate
) as hogemax
    ON hoge.hogedate = hogemax.hogedate
    AND hoge.hogetime = hogemax.hogetime

良いですね。
各日の最新時間の値が取得出来ています。

7C273EBD-AB33-4B5D-BBF2-328067B0633D

さいごに

本日は Athena で S3 バケット内のファイル名に含まれるタイムスタンプから同一日付の新しいレコードのみを抽出してみました。

力技感が少しありますが、Athena だけでやるとこんな感じではないでしょうか。
もうちょっと良い方法あるぜという方はぜひ SNS などで私までコメントをください。

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.